Using UNION and UNION ALL in MySQL

Course: MySQL Training

Topic: Using UNION and UNION ALL

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to UNION and UNION ALL

MySQL provides several SQL operators for combining the results of multiple queries. Two important operators are UNION and UNION ALL. They combine rows returned by two or more SELECT statements into a single result set.

These operators are useful when information is stored in separate tables but needs to be displayed together. For example, a company might store current employees and former employees in different tables but need a single list of people. Similarly, an online store might combine products from multiple categories into one result.

The main difference between UNION and UNION ALL is how they handle duplicate rows:

• UNION: Combines the results and removes duplicate rows.
• UNION ALL: Combines the results and retains duplicate rows.

Both operators are part of MySQL's set-operation functionality. The correct choice depends on whether duplicate result rows should be removed or preserved.

Learning Objectives

• Understand the purpose of UNION and UNION ALL.
• Learn the syntax and rules for combining SELECT statements.
• Understand how UNION handles duplicate rows.
• Understand how UNION ALL preserves duplicate rows.
• Compare the performance implications of both operators.
• Use UNION and UNION ALL with WHERE, ORDER BY, and LIMIT.
• Combine data from multiple tables using practical examples.
• Identify common errors and apply SQL best practices.

2. What Is UNION in MySQL?

2.1 Definition

UNION is a SQL set operator that combines the rows returned by multiple SELECT statements into one result set. By default, it removes duplicate rows from the combined results.

A row is considered a duplicate when all selected column values match the corresponding values of another row in the combined result. UNION does not remove rows merely because one selected column has the same value.

2.2 Basic Syntax

SELECT column1, column2
FROM table1
WHERE condition1
UNION
SELECT column1, column2
FROM table2
WHERE condition2;

Explanation:

• SELECT: Retrieves the required columns from a table.
• FROM: Specifies the source table.
• WHERE: Filters rows according to a condition.
• UNION: Combines the results and removes duplicate rows.

2.3 Simple UNION Example

Suppose two tables contain customer names from different business locations.

Table: customers_mumbai

Table: customers_pune

To combine the customer names and remove duplicate name values, use:

SELECT customer_name
FROM customers_mumbai
UNION
SELECT customer_name
FROM customers_pune;

Possible result:

Priya appears in both source tables, but only one Priya row appears in the combined result because UNION removes duplicate selected rows.

Note: SQL result order is not guaranteed unless an ORDER BY clause is specified.

3. What Is UNION ALL in MySQL?

3.1 Definition

UNION ALL combines the rows returned by multiple SELECT statements without removing duplicates. Every qualifying row from each query is included in the combined result.

This operator is useful when all records must be preserved, such as transaction histories, audit records, event data, or reports where repeated values are meaningful.

3.2 Basic Syntax

SELECT column1, column2
FROM table1
WHERE condition1
UNION ALL
SELECT column1, column2
FROM table2
WHERE condition2;

3.3 Simple UNION ALL Example

Using the same customer tables, execute:

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune;

Possible result:

Priya appears twice because UNION ALL preserves rows from both SELECT statements.

3.4 Why Is UNION ALL Useful?

• It preserves all rows from the source queries.
• It is appropriate when duplicate rows represent separate events or records.
• It avoids the work needed to eliminate duplicate result rows.
• It can be useful for combining large datasets when duplicate removal is unnecessary.

4. Difference Between UNION and UNION ALL

4.1 Example Comparing Both Operators

-- UNION removes duplicate rows
SELECT 10 AS number_value
UNION
SELECT 10 AS number_value;

-- UNION ALL retains duplicate rows
SELECT 10 AS number_value
UNION ALL
SELECT 10 AS number_value;

UNION result:

UNION ALL result:

The first query returns one distinct row. The second query returns two rows.

5. Rules for Using UNION and UNION ALL

5.1 Same Number of Columns

Each SELECT statement must return the same number of columns.

Incorrect example:

SELECT customer_id, customer_name
FROM customers_mumbai
UNION
SELECT customer_name
FROM customers_pune;

This query is invalid because the first SELECT returns two columns while the second returns only one.

Correct example:

SELECT customer_id, customer_name
FROM customers_mumbai
UNION
SELECT customer_id, customer_name
FROM customers_pune;

5.2 Corresponding Columns Must Have Compatible Data Types

The columns in corresponding positions should contain compatible types. MySQL determines the result column types using the expressions from the participating SELECT statements.

For example, combining integer identifiers with integer identifiers is straightforward. Combining numeric values with text values may cause implicit conversions or unexpected results, so explicit conversion can be appropriate when the intended meaning is clear.

SELECT CAST(customer_id AS CHAR) AS record_value
FROM customers_mumbai
UNION ALL
SELECT customer_name AS record_value
FROM customers_pune;

Here, the customer identifier is converted to text so that the output represents both identifiers and names as textual values. In real applications, avoid combining unrelated data concepts into one column unless there is a clear requirement.

5.3 Column Positions Must Match

UNION combines columns by their positions, not by matching their names. The first column from one SELECT corresponds to the first column from the next SELECT, the second corresponds to the second, and so on.

Example:

SELECT customer_id, customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_id, customer_name
FROM customers_pune;

Both queries place customer_id first and customer_name second, so the output has a consistent meaning.

5.4 Output Column Names

The result column names are generally taken from the first SELECT statement. Use aliases in the first SELECT to give the combined result meaningful column names.

SELECT customer_name AS person_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune;

The resulting column is named person_name.

5.5 Use ORDER BY for Final Sorting

UNION and UNION ALL do not guarantee the order of the combined rows. To sort the final result, place ORDER BY after the last SELECT statement.

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune
ORDER BY customer_name ASC;

This sorts the combined names in ascending order.

6. Using UNION with WHERE Conditions

Each SELECT statement can have its own WHERE clause to filter records before the results are combined.

6.1 Example: Customers from Selected Cities

SELECT customer_name, city
FROM customers
WHERE city = 'Mumbai'
UNION
SELECT customer_name, city
FROM customers
WHERE city = 'Pune';

This query combines customers from Mumbai and Pune. Since UNION removes identical result rows, only distinct combinations of customer_name and city remain.

6.2 Example Using UNION ALL

SELECT order_id, order_total
FROM orders
WHERE order_total >= 5000
UNION ALL
SELECT order_id, order_total
FROM orders
WHERE order_total < 1000;

This combines high-value orders and low-value orders. Because the conditions do not overlap, the same order normally will not appear in both branches. UNION ALL avoids unnecessary duplicate elimination in this example.

6.3 Important Condition Design

When combining filtered queries, check whether the conditions overlap. If they overlap, the same row may be returned by multiple branches. UNION removes identical complete result rows, while UNION ALL preserves them.

7. Using UNION Across Multiple Tables

UNION can combine results from two or more tables, provided each SELECT returns the required number of compatible columns in the same order.

7.1 Example: Combining Employee Lists

Suppose a company stores permanent and contract employees separately.

Table: permanent_employees

Table: contract_employees

To create a distinct combined list of employee IDs and names:

SELECT employee_id, employee_name
FROM permanent_employees
UNION
SELECT employee_id, employee_name
FROM contract_employees;

Because employee_id differs between the two records for Meera, both complete rows remain in the result. UNION removes duplicates only when all selected column values match, not merely when the employee name matches.

7.2 Combine All Employee Records

SELECT employee_id, employee_name
FROM permanent_employees
UNION ALL
SELECT employee_id, employee_name
FROM contract_employees;

UNION ALL includes every row from both employee tables, even if two rows contain exactly the same employee ID and employee name.

7.3 Add a Source Label

A source label can identify which table each record came from.

SELECT
    employee_id,
    employee_name,
    'Permanent' AS employment_type
FROM permanent_employees
UNION ALL
SELECT
    employee_id,
    employee_name,
    'Contract' AS employment_type
FROM contract_employees;

The output includes a third column that identifies the source category. Because the employment_type values differ, rows from different categories are not identical across all three selected columns.

8. Using UNION with Different Column Names

The source tables do not need to use identical column names. The number of selected columns, their positions, and their compatible types are what matter.

For example, one table might use full_name while another uses customer_name.

SELECT full_name AS person_name
FROM staff_directory
UNION ALL
SELECT customer_name AS person_name
FROM customer_directory;

Both SELECT statements return one compatible column. The alias in the first SELECT determines the result column name.

Always ensure that corresponding columns have the same intended meaning. A syntactically valid query can still produce misleading output if unrelated columns are combined.

9. Using UNION with Calculated Columns

UNION can combine expressions, constants, and calculated values, provided the SELECT statements return compatible result columns.

9.1 Example: Combining Sales Totals

SELECT
    'Online' AS sales_channel,
    SUM(order_total) AS total_sales
FROM online_orders
UNION ALL
SELECT
    'Store' AS sales_channel,
    SUM(order_total) AS total_sales
FROM store_orders;

This query returns one row for each sales channel with its total sales. The constant labels make the source of each total clear.

9.2 Example: Combining Current and Archived Records

SELECT
    order_id,
    order_date,
    order_total,
    'Current' AS record_source
FROM current_orders
UNION ALL
SELECT
    order_id,
    order_date,
    order_total,
    'Archived' AS record_source
FROM archived_orders;

This approach can be useful when current and archived orders are stored separately. Before using it, verify that the two tables have compatible definitions and that the same logical order is not unintentionally stored in both tables.

10. Using ORDER BY with UNION and UNION ALL

To sort the complete combined result, use ORDER BY after the final SELECT statement.

10.1 Sort in Ascending Order

SELECT customer_name
FROM customers_mumbai
UNION
SELECT customer_name
FROM customers_pune
ORDER BY customer_name ASC;

10.2 Sort in Descending Order

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune
ORDER BY customer_name DESC;

10.3 Sort by an Output Alias

SELECT
    order_id,
    order_total AS total_amount
FROM online_orders
UNION ALL
SELECT
    order_id,
    order_total AS total_amount
FROM store_orders
ORDER BY total_amount DESC;

The alias from the first SELECT can be used to refer to the corresponding result column.

10.4 Sorting Individual SELECT Statements

If an individual SELECT needs its own ORDER BY or LIMIT to determine which rows it contributes, use parentheses around that query expression where required by MySQL syntax.

(SELECT customer_name
 FROM customers_mumbai
 ORDER BY customer_name
 LIMIT 5)
UNION ALL
(SELECT customer_name
 FROM customers_pune
 ORDER BY customer_name
 LIMIT 5)
ORDER BY customer_name;

The inner ORDER BY and LIMIT determine the selected rows from each branch. The final ORDER BY sorts the combined result. Without the final ORDER BY, the overall output order is not guaranteed.

11. Using LIMIT with UNION and UNION ALL

LIMIT restricts the number of rows returned by a query expression. When applied to the complete UNION result, it limits the combined result set.

11.1 Example: Return the First Ten Rows

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune
ORDER BY customer_name
LIMIT 10;

This returns at most ten rows from the sorted combined result.

11.2 LIMIT Within Each Query

When the goal is to select a limited number of rows from each source before combining them, use parentheses around each SELECT expression.

(SELECT order_id, order_total
 FROM online_orders
 ORDER BY order_total DESC
 LIMIT 5)
UNION ALL
(SELECT order_id, order_total
 FROM store_orders
 ORDER BY order_total DESC
 LIMIT 5)
ORDER BY order_total DESC;

This retrieves up to five high-value orders from each source and then sorts those rows together. The final output may contain up to ten rows.

12. Performance Differences Between UNION and UNION ALL

UNION often requires additional processing to detect and remove duplicate rows. UNION ALL does not perform this duplicate-elimination step and can therefore be more efficient, especially when combining large result sets.

However, actual performance depends on the query, data size, MySQL version, execution plan, and available resources. Do not assume that one operator will always be faster in every situation.

12.1 Why Can UNION Be Slower?

• MySQL must identify duplicate result rows.
• Duplicate elimination can require additional processing and temporary structures.
• Large result sets may increase memory usage and processing costs.
• The amount of extra work depends on the execution plan and result characteristics.

12.2 Why Can UNION ALL Be Faster?

• It preserves rows without checking whether the combined rows are duplicates.
• It can avoid the extra work needed for duplicate elimination.
• It is appropriate when all source rows must be preserved.

12.3 Compare Execution Plans

EXPLAIN
SELECT customer_name
FROM customers_mumbai
UNION
SELECT customer_name
FROM customers_pune;

EXPLAIN
SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune;

Review the execution plans and, where appropriate, test both queries using representative data. Compare execution time, rows processed, temporary-table activity, and resource usage.

12.4 Best Performance Practice

Use UNION ALL when duplicates must be preserved or when the branches are known to be disjoint. Use UNION when the requirement is to eliminate duplicate complete rows. Do not replace UNION with UNION ALL solely for speed if duplicate removal is necessary for correctness.

13. UNION vs. JOIN

UNION and JOIN serve different purposes. UNION combines rows vertically, while JOIN combines related columns horizontally by matching records according to a join condition.

13.1 UNION Example

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune;

This places customer names from the two query results into one column.

13.2 JOIN Example

SELECT
    o.order_id,
    c.customer_name,
    o.order_total
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id;

This combines order columns with customer columns by matching customer IDs.

14. Combining More Than Two SELECT Statements

MySQL allows multiple SELECT statements to be combined in a single UNION or UNION ALL expression, as long as each branch follows the column-count and type-compatibility rules.

14.1 Example with Three Tables

SELECT product_name
FROM electronics_products
UNION
SELECT product_name
FROM furniture_products
UNION
SELECT product_name
FROM clothing_products;

This creates a combined list of distinct product-name values from three tables.

14.2 Preserve All Rows from Three Tables

SELECT product_name
FROM electronics_products
UNION ALL
SELECT product_name
FROM furniture_products
UNION ALL
SELECT product_name
FROM clothing_products;

This includes every qualifying row from all three tables, including repeated product names.

14.3 Mixed UNION Operations

MySQL permits combining UNION ALL and UNION DISTINCT behavior in one expression. Because duplicate elimination affects the result, mixed expressions should be written carefully and tested against the intended outcome.

For clarity, prefer simple combinations when possible. If different stages require different duplicate-handling behavior, use parentheses or derived tables to make the intended grouping explicit.

15. Handling Duplicate Rows Correctly

Understanding what counts as a duplicate is one of the most important concepts when working with UNION.

15.1 Duplicate Values in One Column

SELECT 'Mumbai' AS city
UNION
SELECT 'Mumbai' AS city;

Only one row is returned because the complete selected row is identical.

15.2 Same Name, Different ID

SELECT 1 AS employee_id, 'Meera' AS employee_name
UNION
SELECT 2 AS employee_id, 'Meera' AS employee_name;

Two rows are returned because the employee IDs differ. Although the employee_name values match, the complete rows are not duplicates.

15.3 Duplicate Rows Across Different Tables

SELECT product_id, product_name
FROM products_current
UNION
SELECT product_id, product_name
FROM products_backup;

If both tables contain identical product_id and product_name values, UNION removes the repeated result row. If the IDs differ, the rows remain separate even when their product names match.

15.4 Why DISTINCT Business Entities Need Care

UNION removes duplicate result rows, not necessarily duplicate real-world entities. For example, two customers may share the same name but have different customer IDs. If the requirement is to identify unique customers, use an appropriate unique identifier rather than relying on names alone.

16. Common Errors When Using UNION and UNION ALL

16.1 Different Number of Columns

Problem: One SELECT returns more columns than another.

-- Incorrect
SELECT customer_id, customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune;

Solution: Select the same number of columns in each branch.

SELECT customer_id, customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_id, customer_name
FROM customers_pune;

16.2 Incompatible Data Types

Problem: Corresponding columns have different or incompatible types.

Solution: Review the column meanings and use explicit CAST or CONVERT when conversion is appropriate. Avoid combining values with unrelated meanings just to make a query run.

16.3 Unexpected Duplicate Removal

Problem: UNION returns fewer rows than expected.

Cause: Identical complete rows have been removed.

Solution: Use UNION ALL when every row must be preserved. If the requirement is to distinguish sources, add a suitable source-label column.

16.4 Unexpected Duplicate Rows

Problem: UNION ALL returns repeated rows.

Cause: UNION ALL preserves duplicates by design.

Solution: Use UNION if identical complete result rows must be eliminated, or apply an explicit deduplication strategy based on a unique business key if the requirement concerns duplicate entities rather than identical rows.

16.5 Unexpected Result Order

Problem: Rows appear in a different order than expected.

Cause: SQL does not guarantee output ordering without ORDER BY.

Solution: Add ORDER BY to the complete combined query.

16.6 Incorrect Column Mapping

Problem: The output values appear under misleading column names.

Cause: UNION aligns columns by position, not by their names.

Solution: Put corresponding columns in the same order and use consistent aliases in the first SELECT statement.

17. Best Practices for UNION and UNION ALL

• Use UNION when duplicate complete rows should be removed.
• Use UNION ALL when all qualifying rows must be retained.
• Ensure every SELECT returns the same number of columns.
• Keep corresponding columns in the same order and with compatible data types.
• Use meaningful aliases in the first SELECT statement.
• Add a source-label column when identifying the origin of a row is important.
• Use ORDER BY to guarantee the final output order.
• Apply filters in the individual SELECT statements when appropriate to reduce unnecessary rows.
• Use EXPLAIN and realistic tests to evaluate query performance.
• Do not use UNION to remove duplicate business entities unless the selected columns correctly represent the intended uniqueness rule.
• Review whether source tables overlap before using UNION ALL in a combined report.
• Prefer readable query formatting and clear table aliases.

18. Practical Project: Combine Customer Records

18.1 Project Scenario

A business maintains separate customer tables for Mumbai and Pune. The reporting team needs a combined customer list. Sometimes it needs a list of distinct names, and sometimes it needs every record with its source location.

18.2 Create Sample Tables

CREATE DATABASE union_practice;
USE union_practice;

CREATE TABLE customers_mumbai (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE customers_pune (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

18.3 Insert Sample Records

INSERT INTO customers_mumbai
    (customer_id, customer_name, city)
VALUES
    (1, 'Amit', 'Mumbai'),
    (2, 'Priya', 'Mumbai'),
    (3, 'Rahul', 'Mumbai');

INSERT INTO customers_pune
    (customer_id, customer_name, city)
VALUES
    (101, 'Priya', 'Pune'),
    (102, 'Neha', 'Pune'),
    (103, 'Vikas', 'Pune');

18.4 Task 1: Combine Distinct Customer Names

SELECT customer_name
FROM customers_mumbai
UNION
SELECT customer_name
FROM customers_pune
ORDER BY customer_name;

Expected distinct names: Amit, Neha, Priya, Rahul, and Vikas.

18.5 Task 2: Combine All Customer Names

SELECT customer_name
FROM customers_mumbai
UNION ALL
SELECT customer_name
FROM customers_pune
ORDER BY customer_name;

This returns six rows, including both occurrences of Priya because the two source records are retained.

18.6 Task 3: Include the Source City

SELECT
    customer_id,
    customer_name,
    city,
    'Mumbai Table' AS source_table
FROM customers_mumbai
UNION ALL
SELECT
    customer_id,
    customer_name,
    city,
    'Pune Table' AS source_table
FROM customers_pune
ORDER BY customer_name;

This query returns all customer records and identifies their source table.

18.7 Task 4: Filter the Combined Result

SELECT customer_name, city
FROM customers_mumbai
WHERE customer_name LIKE 'P%'
UNION ALL
SELECT customer_name, city
FROM customers_pune
WHERE customer_name LIKE 'P%'
ORDER BY customer_name;

This returns matching customer records whose names begin with P from both tables.

18.8 Project Learning Outcomes

• Combine query results from multiple tables.
• Understand the effect of duplicate removal.
• Apply filtering to individual query branches.
• Add aliases and source labels to combined results.
• Sort combined output using ORDER BY.

19. Interview Questions and Answers

Q1. What is UNION in MySQL?

Answer: UNION combines the results of multiple SELECT statements into one result set and removes duplicate complete rows.

Q2. What is UNION ALL?

Answer: UNION ALL combines the results of multiple SELECT statements and retains all rows, including duplicates.

Q3. What is the main difference between UNION and UNION ALL?

Answer: UNION removes duplicate rows, while UNION ALL preserves them.

Q4. Which operator is usually faster?

Answer: UNION ALL is often faster because it does not need to eliminate duplicate rows. Actual performance should be confirmed with realistic tests.

Q5. Can UNION combine more than two SELECT statements?

Answer: Yes. Multiple SELECT statements can be combined using UNION or UNION ALL as long as the column requirements are satisfied.

Q6. Must column names be identical in every SELECT statement?

Answer: No. The number and order of columns must match, and corresponding data types must be compatible. Result column names are generally taken from the first SELECT statement.

Q7. Must all SELECT statements return the same number of columns?

Answer: Yes. Each SELECT in the UNION expression must return the same number of columns.

Q8. Does UNION remove rows with the same value in just one column?

Answer: Not necessarily. UNION removes duplicate complete result rows. If another selected column differs, both rows remain.

Q9. Can UNION be used with WHERE?

Answer: Yes. Each SELECT statement can use its own WHERE condition to filter the rows it contributes to the combined result.

Q10. How do you sort a UNION result?

Answer: Place ORDER BY after the final SELECT statement to sort the combined result.

Q11. What is the difference between UNION and JOIN?

Answer: UNION combines result rows vertically, while JOIN combines related columns horizontally based on matching conditions.

Q12. Can UNION ALL return duplicate rows?

Answer: Yes. Preserving duplicate rows is its defining behavior.

Q13. What happens if the SELECT statements use different data types?

Answer: MySQL determines compatible result types using its type-conversion rules. Explicit conversions can help when they are appropriate to the intended data meaning.

Q14. How do you identify which table produced a row?

Answer: Include a constant source-label column in each SELECT statement, such as 'Mumbai' or 'Pune'.

Q15. When should UNION ALL be preferred?

Answer: Prefer UNION ALL when all rows must be preserved or when the result sets are known to be disjoint and duplicate elimination is unnecessary.

20. Practice Exercises

Exercise 1: Basic UNION

Create two tables named online_students and classroom_students. Insert several student names, including at least one repeated name. Use UNION to create a distinct list of names.

Exercise 2: Basic UNION ALL

Use the same tables to return every student-name row using UNION ALL. Compare the number of rows with the result from Exercise 1.

Exercise 3: Employee Records

Combine full-time and part-time employee tables using employee_id and employee_name. Add an employment_type column to identify the source.

Exercise 4: Sorting and Filtering

Combine products from two tables, select only products whose prices are above a specified threshold, and sort the final result by price.

Exercise 5: Performance Comparison

Run equivalent UNION and UNION ALL queries on representative data. Use EXPLAIN to inspect the execution plans and compare performance. Explain why the results differ when duplicate rows exist.

Exercise 6: Identify the Correct Operator

For each scenario, decide whether UNION or UNION ALL is appropriate:

1. Creating a distinct list of city names from two tables.
2. Combining all financial transaction records from two separate periods.
3. Combining employee records while preserving all source rows.
4. Producing a list of distinct complete product rows.
5. Combining event logs where repeated events must be retained.

21. Quick Revision Notes

• UNION combines results from multiple SELECT statements.
• UNION removes duplicate complete rows.
• UNION ALL preserves all rows, including duplicates.
• Both operators require the same number of selected columns in every branch.
• Corresponding columns should have compatible data types and matching meanings.
• Columns are matched by position, not by their names.
• The combined result's column names generally come from the first SELECT.
• ORDER BY after the final SELECT sorts the complete result.
• UNION may require additional processing for duplicate elimination.
• UNION ALL is often faster when duplicate removal is unnecessary.
• UNION is different from JOIN: UNION appends rows, while JOIN combines columns.
• A source-label column can identify which table contributed each row.
• Use EXPLAIN and realistic performance tests to evaluate queries.

22. Conclusion

UNION and UNION ALL are essential MySQL operators for combining data returned by multiple SELECT statements. They are especially useful when information is stored across separate tables or when multiple filtered queries must produce one result set.

The key distinction is duplicate handling. UNION removes duplicate complete rows, while UNION ALL preserves every qualifying row. UNION can be appropriate when a distinct result is required, whereas UNION ALL is often more efficient when all rows must be retained.

For reliable results, ensure that each SELECT returns the same number of columns, keep corresponding columns in a consistent order, use compatible data types, and add ORDER BY when a specific output order is required. Choose the operator according to the intended result rather than performance alone.

Continue Learning: Explore the MySQL Training Course to strengthen your SQL skills, or register for a course demo to learn more about the training.